﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace CSLAGenTool
{
    /* Default value: {Description}
     * [Value]
     * [Session.LoginID]
     */

    public partial class FrmMain : Form
    {
        #region variants
        //---------------------------------------------------------------
        private Data data = null;
        private DataAccess dataAccess = null;
        private string connectionStr = "";
        private string appPath = Application.StartupPath;
        private string tmpFetchColumns = "if (!(dataReader[\"{0}\"] is DBNull))\r\n{1} = ({2})dataReader[\"{3}\"];\r\n";
        private string tmpDoInsertColumns = "db.AddInParameter(cmd, \"@{0}\", SqlDbType.{1}, {2});\r\n";
        private string NameSpace = "";
        private string BOObject = "";
        private string BOObjectVariant = "";
        private string PrimaryKey = "";
        private string PrimaryKeyVariant = "";
        private string PrimaryKeyVariantNotDotValue = "";
        private string PrimarySQLDataType = "";
        private string NewPrimaryKey = "";
        private string KeyColumn = "";
        private string ValueColumn = "";
        private string KeyDataType = "";
        private string ValueDataType = "";
        private string CMDInsert = "";
        private string CMDUpdate = "";
        private string CMDFetch = "";
        private string CMDDelete = "";
        private string CMDFetchBOCollection = "";
        private string CMDFetchBOCollectionWithPagging = "";
        private string CMDCountOfCollection = "";
        private string CMDFetchBOList = "";
        private string CMDFetchBOListWithPaging = "";
        private string CMDCondition = "";
        private string Columns = "";
        private string FetchColumns = "";
        private string DoInsertColumns = "";
        private string GetMaxID = "";
        private string DoUpdateColumns = "";
        private string Init_InsertParametters = "";
        private string Add_InsertParametters = "";
        private string Add_Output = "";
        private string Add_UpdateOutput = "";
        private string Init_UpdateParametters = "";
        private string Add_UpdateParametters = "";
        private string GetWhere = "";
        private TableInfo table = null;
        private TableInfo tableInfo = null;
        //---------------------------------------------------------------
        #endregion variants

        #region form
        //---------------------------------------------------------------
        public FrmMain()
        {
            InitializeComponent();
            this.Load += new EventHandler(FrmMain_Load);
            this.Shown += new EventHandler(FrmMain_Shown);
            if (!appPath.EndsWith("\\"))
                appPath += "\\";
            Init();
        }

        private void FrmMain_Load(object sender, EventArgs e)
        {
            txtNamespace.Enter += new EventHandler(Control_Enter);
            txtSolutionPath.Enter += new EventHandler(Control_Enter);
            txtBOName.Enter += new EventHandler(Control_Enter);
            txtBOCollectionName.Enter += new EventHandler(Control_Enter);
            txtBOListName.Enter += new EventHandler(Control_Enter);
            txtCondition.Enter += new EventHandler(Control_Enter);

            cmbPrimaryKey.Enter += new EventHandler(Control_Enter);
            cmbKeyColumn.Enter += new EventHandler(Control_Enter);
            cmbValueColumn.Enter += new EventHandler(Control_Enter);
            //---------------------
            txtNamespace.Leave += new EventHandler(Control_Leave);
            txtSolutionPath.Leave += new EventHandler(Control_Leave);
            txtBOName.Leave += new EventHandler(Control_Leave);
            txtBOCollectionName.Leave += new EventHandler(Control_Leave);
            txtBOListName.Leave += new EventHandler(Control_Leave);
            txtCondition.Leave += new EventHandler(Control_Leave);
            //			
            cmbTableName.SelectedIndexChanged += new EventHandler(cmbTableName_SelectedIndexChanged);
            txtBOName.DoubleClick += new EventHandler(txtBOName_DoubleClick);
            txtBOCollectionName.DoubleClick += new EventHandler(txtBOCollectionName_DoubleClick);
            txtBOListName.DoubleClick += new EventHandler(txtBOListName_DoubleClick);
            //
            txtBOName.TextChanged += new EventHandler(txtBOName_TextChanged);
            //
            btnGenerate.Click += new EventHandler(btnGenerate_Click);
            btnReload.Click += new EventHandler(btnReload_Click);
            //
            LoadTableName();
        }

        private void FrmMain_Shown(object sender, EventArgs e)
        {
            cmbTableName.Focus();
        }
        //---------------------------------------------------------------
        #endregion form

        #region events
        //---------------------------------------------------------------
        private void txtBOName_DoubleClick(object sender, EventArgs e)
        {
            chkBO.Checked = !chkBO.Checked;
        }

        private void txtBOCollectionName_DoubleClick(object sender, EventArgs e)
        {
            chkBOCollection.Checked = !chkBOCollection.Checked;
        }

        private void txtBOListName_DoubleClick(object sender, EventArgs e)
        {
            chkBOList.Checked = !chkBOList.Checked;
        }

        private void txtBOName_TextChanged(object sender, EventArgs e)
        {
            txtBOCollectionName.Text = txtBOName.Text + "Collection";
            txtBOListName.Text = txtBOName.Text + "List";
        }

        private void Control_Enter(object sender, EventArgs e)
        {
            Control control = (Control)sender;
            control.BackColor = Color.FromArgb(255, 255, 192);
        }

        private void Control_Leave(object sender, EventArgs e)
        {
            Control control = (Control)sender;
            control.BackColor = Color.White;
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void cmbTableName_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                txtBOName.Text = "";
                txtBOCollectionName.Text = "";
                txtBOListName.Text = "";

                cmbPrimaryKey.Items.Clear();
                cmbKeyColumn.Items.Clear();
                cmbValueColumn.Items.Clear();

                cmbPrimaryKey.Text = "";
                cmbKeyColumn.Text = "";
                cmbValueColumn.Text = "";

                this.lvColumn.Items.Clear();

                if (this.cmbDBType.SelectedIndex == 0)
                    this.table = data.GetTableInfo(cmbTableName.Text);
                else
                    if (this.cmbDBType.SelectedIndex == 1)
                        this.table = dataAccess.GetTableInfo(cmbTableName.Text);

                int i = 0;
                int iSub = 0;
                bool isNotSpecialColumn = true;
                int iPK = 0;

                string columnName = "";
                for (i = 0; i < this.table.columnInfos.Count; i++)
                {
                    columnName = this.table.columnInfos[i].columnName;
                    isNotSpecialColumn = columnName != "USRC";
                    isNotSpecialColumn &= columnName != "DATC";
                    isNotSpecialColumn &= columnName != "USRM";
                    isNotSpecialColumn &= columnName != "DATM";
                    isNotSpecialColumn &= columnName != "DELE";

                    if (isNotSpecialColumn)
                    {
                        cmbPrimaryKey.Items.Add(columnName);
                        cmbKeyColumn.Items.Add(columnName);
                        cmbValueColumn.Items.Add(columnName);
                        if (this.table.columnInfos[i].isPrimaryKey)
                            iPK = i - iSub;
                    }
                    else
                        iSub++;

                    ListViewItem item = new ListViewItem(columnName);
                    item.Checked = true;
                    this.lvColumn.Items.Add(item);
                }

                cmbPrimaryKey.SelectedIndex = iPK;
                cmbKeyColumn.SelectedIndex = 0;

                txtBOName.Text = cmbTableName.Text + "BO";
                txtBOCollectionName.Text = cmbTableName.Text + "BOCollection";
                txtBOListName.Text = cmbTableName.Text + "BOList";
            }
            catch (Exception ex)
            {
                MessageBox.Show("cmbTableName_SelectedIndexChanged:\r\n" + ex.Message);
            }
        }

        private void btnGenerate_Click(object sender, EventArgs e)
        {
            GenByStoreProcedure();

            cmbTableName.Focus();
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            Reload();
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            lblStatus.Visible = false;
            timer1.Enabled = false;
        }
        //---------------------------------------------------------------
        #endregion events

        #region function
        //---------------------------------------------------------------
        private void Init()
        {
            try
            {
                data = new Data(appPath);
                dataAccess = new DataAccess(appPath);
                string[] sTmp = File.ReadAllLines(appPath + "config.txt");
                connectionStr = sTmp[0];
                txtNamespace.Text = sTmp[1];
                txtSolutionPath.Text = sTmp[2];

                this.cmbDBType.SelectedIndex = 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error in Init() Function:\r\n" + ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void LoadTableName()
        {
            try
            {
                cmbTableName.Enabled = true;
                string[] arrTableName = null;
                if (this.cmbDBType.SelectedIndex == 0)
                    arrTableName = data.GetAllTableNameOrderByName();
                else
                    if (this.cmbDBType.SelectedIndex == 1)
                        arrTableName = dataAccess.GetAllTableNameOrderByName();

                cmbTableName.Items.Clear();
                cmbTableName.Text = "";
                foreach (string tableName in arrTableName)
                {
                    if (tableName != "sysdiagrams")
                        cmbTableName.Items.Add(tableName);
                }
                cmbTableName.SelectedIndex = -1;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error in LoadTableName() Function:\r\n" + ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                cmbTableName.Enabled = false;
            }
        }

        private void Reload()
        {
            Init();
            LoadTableName();
            //
            cmbTableName.SelectedIndex = -1;
            txtBOName.Text = "";
            txtBOCollectionName.Text = "";
            txtBOListName.Text = "";
            cmbPrimaryKey.Items.Clear();
            cmbKeyColumn.Items.Clear();
            cmbValueColumn.Items.Clear();
            cmbPrimaryKey.Text = "";
            cmbKeyColumn.Text = "";
            cmbValueColumn.Text = "";
        }

        private void createFile(string tmpPath, string path)
        {
            StreamReader sr = new StreamReader(tmpPath, Encoding.UTF8);
            StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8);
            string s = "";
            while (!sr.EndOfStream)
            {
                s = sr.ReadLine();
                s = s.Replace("<%NameSpace%>", NameSpace);
                s = s.Replace("<%BOObject%>", BOObject);
                s = s.Replace("<%BOObjectVariant%>", BOObjectVariant);
                s = s.Replace("<%CMDInsert%>", CMDInsert);
                s = s.Replace("<%CMDUpdate%>", CMDUpdate);
                s = s.Replace("<%CMDFetch%>", CMDFetch);
                s = s.Replace("<%CMDDelete%>", CMDDelete);
                s = s.Replace("<%CMDDeletePhysical%>", "\"\"");
                s = s.Replace("<%CMDFetchBOCollection%>", CMDFetchBOCollection);
                s = s.Replace("<%CMDFetchBOCollectionWithPagging%>", CMDFetchBOCollectionWithPagging);
                s = s.Replace("<%CMDFetchBOList%>", CMDFetchBOList);
                s = s.Replace("<%CMDFetchBOListWithPaging%>", CMDFetchBOListWithPaging);
                s = s.Replace("<%CMDCondition%>", CMDCondition);
                s = s.Replace("<%KeyColumn%>", KeyColumn);
                s = s.Replace("<%ValueColumn%>", ValueColumn);
                s = s.Replace("<%KeyDataType%>", KeyDataType);
                s = s.Replace("<%ValueDataType%>", ValueDataType);
                s = s.Replace("<%PrimaryKeyVariant%>", PrimaryKeyVariant);
                s = s.Replace("<%PrimaryKeyVariantNotDotValue%>", PrimaryKeyVariantNotDotValue);
                s = s.Replace("<%PrimaryKey%>", PrimaryKey);
                s = s.Replace("<%NewPrimaryKey%>", NewPrimaryKey);
                s = s.Replace("<%PrimaryVariantData%>", tableInfo.variantType);
                s = s.Replace("<%Columns%>", Columns);
                s = s.Replace("<%FetchColumns%>", FetchColumns);
                s = s.Replace("<%DoInsertColumns%>", DoInsertColumns);
                s = s.Replace("<%DoUpdateColumns%>", DoUpdateColumns);
                s = s.Replace("<%CMDCountOfCollection%>", CMDCountOfCollection);
                s = s.Replace("<%Init_InsertParametters%>", Init_InsertParametters);
                s = s.Replace("<%Add_InsertParametters%>", Add_InsertParametters);
                s = s.Replace("<%Add_Output%>", Add_Output);
                s = s.Replace("<%Add_UpdateOutput%>", Add_UpdateOutput);
                s = s.Replace("<%Init_UpdateParametters%>", Init_UpdateParametters);
                s = s.Replace("<%Add_UpdateParametters%>", Add_UpdateParametters);
                s = s.Replace("<%TableName%>", this.tableInfo.tableName);
                s = s.Replace("<%GetWhere%>", this.GetWhere);
                s = s.Replace("<%CMDGetMaxID%>", this.GetMaxID);
                s = s.Replace("<%PrimarySQLDataType%>", this.PrimarySQLDataType);
                sw.WriteLine(s);
            }
            sr.Close();
            sw.Close();
        }

        private string convertToVariant(string s)
        {
            return "_" + s;
        }

        private bool Validate()
        {
            string sError = "";
            int i = 0;
            if (txtNamespace.Text.Trim().Length == 0)
                sError += (++i).ToString() + ". NameSpace is required!\r\n";

            if (txtSolutionPath.Text.Trim().Length == 0)
                sError += (++i).ToString() + ". Solution Path is required!\r\n";
            else
                if (!Directory.Exists(txtSolutionPath.Text))
                    sError += (++i).ToString() + ". Solution Path don't exist!\r\n";

            if (cmbTableName.Text.Length == 0)
                sError += (++i).ToString() + ". Table Name Path is required!\r\n";
            else
                if (cmbPrimaryKey.Items.Count == 0)
                    sError += (++i).ToString() + ". Table Name is invalid!\r\n";
                else
                {
                    if ((chkBO.Checked || chkBOCollection.Checked) && cmbPrimaryKey.Text.Trim().Length == 0)
                        sError += (++i).ToString() + ". Please select Primary Key!\r\n";

                    if (!chkBO.Checked && !chkBOCollection.Checked && !chkBOList.Checked)
                        sError += (++i).ToString() + ". Please check(s) BOName or BOCollection Name or BOList Name!\r\n";

                    if (chkBO.Checked)
                        if (txtBOName.Text.Trim().Length == 0)
                            sError += (++i).ToString() + ". Please enter BO name!\r\n";

                    if (chkBOCollection.Checked)
                        if (txtBOCollectionName.Text.Trim().Length == 0)
                            sError += (++i).ToString() + ". Please enter BOCollection name!\r\n";

                    if (chkBOList.Checked)
                    {
                        if (txtBOListName.Text.Trim().Length == 0)
                            sError += (++i).ToString() + ". Please enter BOList name!\r\n";
                        if (cmbKeyColumn.Text.Trim().Length == 0)
                            sError += (++i).ToString() + ". Please select Key Column!\r\n";
                        if (cmbValueColumn.Text.Trim().Length == 0)
                            sError += (++i).ToString() + ". Please select Value Column!\r\n";
                        if (txtCondition.Text.Trim().Length > 0 && txtCondition.Text.Trim().StartsWith("WHERE"))
                            sError += (++i).ToString() + ". Invalided Condition!\r\n";
                    }
                }

            if (sError != "")
                MessageBox.Show(sError);
            return (sError == "");
        }

        private string GetDefaultValueForVariant(ColumnInfo col)
        {
            string result = "null";

            switch (col.columnName)
            {
                case "USRC":
                    result = "Session.LoginID";
                    break;
                case "DATC":
                    result = "DateTime.MaxValue";
                    break;
                case "DELE":
                    result = "false";
                    break;
            }

            if (result == "null" && string.IsNullOrEmpty(col.description) == false)
            {
                int iStart = -1;
                int iEnd = -1;
                int count = 0;
                int length = col.description.Length;
                for (int i = 0; i < length; i++)
                {
                    if (col.description[i] == '[')
                    {
                        count++;
                        if (iStart == -1)
                            iStart = i;
                    }
                    else
                        if (col.description[i] == ']' && iStart >= 0)
                        {
                            count--;
                            if (count == 0)
                            {
                                iEnd = i;
                                break;
                            }
                        }
                }

                if (iStart >= 0 && iEnd > iStart)
                {
                    result = col.description.Substring(iStart + 1, iEnd - iStart - 1);

                    if (string.IsNullOrEmpty(result))
                        result = "null";

                    if (result != "null" && col.variantType == "string")
                        result = "\"" + result + "\"";
                }
            }

            return result;
        }

        private void CreateSP_BOSelect()
        {
            SqlDatabase db = new SqlDatabase(connectionStr);
            SqlCommand cmd = null;
            string spName = this.BOObject + "_GetBO";
            string scmd = "";
            scmd =
                "IF OBJECT_ID(N'[" + spName + "]') IS NOT NULL\r\n" +
                "   DROP PROCEDURE [" + spName + "]";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
            //
            scmd =
                "CREATE PROCEDURE [" + spName + "]\r\n\r\n" +
                "@WhereExpression NVARCHAR(MAX)\r\n\r\n" +
                "AS\r\n" +
                "SET NOCOUNT ON\r\n" +
                "SET TRANSACTION ISOLATION LEVEL READ COMMITTED\r\n" +
                "DECLARE @SQL nvarchar(MAX)\r\n" +
                "BEGIN\r\n\r\n" +
                "SET @SQL = '\r\n" +
                "SELECT TOP 1\r\n";

            for (int i = 0; i < this.tableInfo.columnInfos.Count; i++)
                scmd += "[" + this.tableInfo.columnInfos[i].columnName + "]" + (i == this.tableInfo.columnInfos.Count - 1 ? "" : ",") + "\r\n";

            scmd +=
                "FROM\r\n" +
                "[" + this.tableInfo.tableName + "]'";
            scmd += "\r\nSET @SQL = @SQL + ' WHERE ' + @WhereExpression\r\n\r\n" +
                "END\r\n" +
                "EXEC sp_executesql @SQL";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
        }

        private void CreateSP_BOInsert()
        {
            SqlDatabase db = new SqlDatabase(connectionStr);
            SqlCommand cmd = null;
            int i = 0;
            string spName = this.BOObject + "_InsertBO";
            string scmd = "";
            scmd =
                "IF OBJECT_ID(N'[" + spName + "]') IS NOT NULL\r\n" +
                "   DROP PROCEDURE [" + spName + "]";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
            //
            scmd =
                "CREATE PROCEDURE [" + spName + "]\r\n";

            ColumnInfo column = null;

            for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
            {
                column = this.tableInfo.columnInfos[i];

                if (!column.isAutoID && column.columnName != "DATC" && column.columnName != "USRM" && column.columnName != "DATM" && column.columnName != "DELE")
                {
                    scmd += "\r\n@" + column.columnName + " " + column.sqlDataType;
                    if (column.stringLength != -1 && column.sqlDataType != "NText" && column.sqlDataType != "Image")
                        scmd += "(" + column.stringLength.ToString() + ")";
                    else
                        if (column.sqlDataType == "Decimal")
                            scmd += column.decimalLength;
                    scmd += (i == (this.tableInfo.columnInfos.Count - 1) ? "" : ",");
                }
            }

            scmd += ",\r\n@New" + this.tableInfo.primaryKey + " " + this.tableInfo.sqlDataType + " OUTPUT,\r\n";
            scmd += "@NewDATC DateTime OUTPUT\r\n\r\n";

            scmd +=
                "AS\r\n" +
                "SET NOCOUNT ON\r\n\r\n" +
                "INSERT INTO [" + this.tableInfo.tableName + "] (\r\n";

            for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
            {
                column = this.tableInfo.columnInfos[i];
                if (!column.isAutoID && column.columnName != "USRM" && column.columnName != "DATM")
                    scmd += "[" + column.columnName + "]" + (i == this.tableInfo.columnInfos.Count - 1 ? "" : ",") + "\r\n";
            }
            scmd += ") VALUES (\r\n";

            for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
            {
                column = this.tableInfo.columnInfos[i];

                if (column.columnName == "DATC")
                    scmd += "GETDATE()" + (i == this.tableInfo.columnInfos.Count - 1 ? "" : ",") + "\r\n";
                else
                    if (column.columnName == "DELE")
                        scmd += "0" + (i == this.tableInfo.columnInfos.Count - 1 ? "" : ",") + "\r\n";
                    else
                        if (!column.isAutoID && column.columnName != "USRM" && column.columnName != "DATM" && column.columnName != "DELE")
                            scmd += "@" + column.columnName + (i == this.tableInfo.columnInfos.Count - 1 ? "" : ",") + "\r\n";
            }
            scmd += ")\r\n";

            if (this.tableInfo.isAutoID)
                scmd += "\r\nSET @New" + this.tableInfo.primaryKey + " = SCOPE_IDENTITY()";
            if (this.tableInfo.HasColumn("DATC"))
                scmd += "\r\nSET @NewDATC = GETDATE()";

            scmd = scmd.Replace(",,", ",");

            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
        }

        private void CreateSP_BOUpdate()
        {
            SqlDatabase db = new SqlDatabase(connectionStr);
            SqlCommand cmd = null;
            int i = 0;
            string spName = this.BOObject + "_UpdateBO";
            string scmd = "";
            scmd =
                "IF OBJECT_ID(N'[" + spName + "]') IS NOT NULL\r\n" +
                "   DROP PROCEDURE [" + spName + "]";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
            //
            scmd =
                "CREATE PROCEDURE [" + spName + "]\r\n";

            ColumnInfo column = null;

            for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
            {
                column = this.tableInfo.columnInfos[i];

                if (column.columnName != "USRC" && column.columnName != "DATC" && column.columnName != "DATM")
                {
                    scmd += "@" + column.columnName + " " + column.sqlDataType;
                    if (column.stringLength != -1 && column.sqlDataType != "NText" && column.sqlDataType != "Image")
                        scmd += "(" + column.stringLength.ToString() + ")";
                    else
                        if (column.sqlDataType == "Decimal")
                            scmd += column.decimalLength;
                    scmd += (i == (this.tableInfo.columnInfos.Count - 1) ? "" : ",") + "\r\n";
                }
            }

            if (this.tableInfo.HasColumn("DATM"))
                scmd += ",@NewDATM DateTime OUTPUT\r\n\r\n";

            scmd +=
                "AS\r\n" +
                "SET NOCOUNT ON\r\n" +
                "UPDATE [" + this.tableInfo.tableName + "] SET\r\n";

            for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
            {
                column = this.tableInfo.columnInfos[i];

                if (column.columnName == "DATM")
                    scmd += "[DATM]" + " = GETDATE()" + (i == this.tableInfo.columnInfos.Count - 1 ? "" : ",") + "\r\n";
                else
                    if (!column.isAutoID && column.columnName != "USRC" && column.columnName != "DATC")
                        scmd += "[" + column.columnName + "]" + " = @" + column.columnName + (i == this.tableInfo.columnInfos.Count - 1 ? "" : ",") + "\r\n";
            }

            scmd += "WHERE\r\n[" + this.tableInfo.primaryKey + "] = @" + this.tableInfo.primaryKey;

            if (this.tableInfo.HasColumn("DATM"))
                scmd += "\r\nSET @NewDATM = GETDATE()";

            scmd = scmd.Replace(",,", ",");

            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
        }

        private void CreateSP_BODelete()
        {
            SqlDatabase db = new SqlDatabase(connectionStr);
            SqlCommand cmd = null;
            string spName = this.BOObject + "_DeleteBO";
            string scmd = "";
            scmd =
                "IF OBJECT_ID(N'[" + spName + "]') IS NOT NULL\r\n" +
                "   DROP PROCEDURE [" + spName + "]";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
            //
            scmd =
                "CREATE PROCEDURE [" + spName + "]\r\n" +
                "@DirectDelete Bit = 0,\r\n" +
                "@" + this.tableInfo.primaryKey + " " + this.tableInfo.sqlDataType + "\r\n" +
                "AS\r\n" +
                "SET NOCOUNT ON\r\n" +
                "IF @DirectDelete = 0\r\n" +
                "BEGIN\r\n" +
                "UPDATE [" + this.tableInfo.tableName + "] SET\r\n" +
                "[DELE] = 1\r\n" +
                "WHERE\r\n" +
                "[" + this.tableInfo.primaryKey + "] = @" + this.tableInfo.primaryKey + "\r\n" +
                "END ELSE\r\n" +
                "BEGIN\r\n" +
                "DELETE FROM [" + this.tableInfo.tableName + "] WHERE [" + this.tableInfo.primaryKey + "] = @" + this.tableInfo.primaryKey + "\r\n" +
                "END";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
        }

        private void CreateSP_BOCollectionSelect()
        {
            SqlDatabase db = new SqlDatabase(connectionStr);
            SqlCommand cmd = null;
            int i = 0;
            string spName = this.BOObject + "_SelectBOCollection";
            string scmd = "";
            scmd =
                "IF OBJECT_ID(N'[" + spName + "]') IS NOT NULL\r\n" +
                "   DROP PROCEDURE [" + spName + "]";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
            //
            scmd =
                "CREATE PROCEDURE [" + spName + "]\r\n" +
                "@OrderColumn VARCHAR(100),\r\n" +
                "@OrderExpression VARCHAR(4) = 'ASC',\r\n" +
                "@WhereExpression NVARCHAR(MAX) = NULL\r\n" +
                "AS\r\n" +
                "SET NOCOUNT ON;\r\n" +
                "SET TRANSACTION ISOLATION LEVEL READ COMMITTED\r\n" +
                "DECLARE @SQL NVARCHAR(MAX)\r\n" +
                "BEGIN\r\n" +
                "SET @SQL = '\r\n" +
                "SELECT\r\n";
            for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
                scmd += "[" + this.tableInfo.columnInfos[i].columnName + "]" + (i == this.tableInfo.columnInfos.Count - 1 ? "" : ",") + "\r\n";
            scmd +=
                "FROM \r\n" +
                "[" + this.tableInfo.tableName + "]'\r\n";
            scmd += "SET @SQL = @SQL + ' ' + @WhereExpression + ' ORDER BY ' + @OrderColumn + ' ' + @OrderExpression\r\n" +
                "END\r\n" +
                "EXEC sp_executesql @SQL";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
        }

        private void CreateSP_GetTotalRows()
        {
            return;

            SqlDatabase db = new SqlDatabase(connectionStr);
            SqlCommand cmd = null;
            string spName = "GetTotalRows";
            string scmd = "";
            scmd =
                "IF OBJECT_ID(N'[" + spName + "]') IS NOT NULL\r\n" +
                "   DROP PROCEDURE [" + spName + "]";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
            //
            scmd =
               "CREATE PROCEDURE [" + spName + "]\r\n" +
               "   @TableName VARCHAR(100),\r\n" +
               "   @ColumnName VARCHAR(100),\r\n" +
               "   @WhereExpression NVARCHAR(MAX) = NULL\r\n" +
               "AS\r\n" +
               "SET NOCOUNT ON;\r\n" +
               "SET TRANSACTION ISOLATION LEVEL READ COMMITTED\r\n" +
               "DECLARE @SQL NVARCHAR(MAX)\r\n" +
               "BEGIN\r\n" +
               "   SET @SQL = 'SELECT COUNT(' + @ColumnName + ') AS [PageNumbers] FROM ' + @TableName + ' ' + @WhereExpression\r\n" +
               "END\r\n" +
               "EXEC sp_executesql @SQL";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
        }

        private void CreateSP_GetBOList()
        {
            SqlDatabase db = new SqlDatabase(connectionStr);
            SqlCommand cmd = null;
            string spName = "GetBOList";
            string scmd = "";
            scmd =
                "IF OBJECT_ID(N'[" + spName + "]') IS NOT NULL\r\n" +
                "   DROP PROCEDURE [" + spName + "]";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
            //
            scmd =
               "CREATE PROCEDURE [" + spName + "]\r\n" +
               "@TableName VARCHAR(100),\r\n" +
               "@KeyColumn VARCHAR(100),\r\n" +
               "@ValueColumn VARCHAR(100),\r\n" +
               "@WhereExpression NVARCHAR(MAX) = NULL,\r\n" +
               "@OrderColumn VARCHAR(100),\r\n" +
               "@OrderExpression VARCHAR(4)\r\n" +
               "AS\r\n" +
               "SET NOCOUNT ON;\r\n" +
               "SET TRANSACTION ISOLATION LEVEL READ COMMITTED\r\n" +
               "DECLARE @SQL NVARCHAR(MAX)\r\n" +
               "BEGIN\r\n" +
               "SET @SQL = 'SELECT [' + @KeyColumn + '], [' + @ValueColumn + '] FROM ' + @TableName + ' ' + @WhereExpression + ' ORDER BY ' + @OrderColumn + ' ' + @OrderExpression\r\n" +
               "END\r\n" +
               "EXEC sp_executesql @SQL";
            cmd = new SqlCommand(scmd);
            db.ExecuteNonQuery(cmd);
        }

        private void GenByStoreProcedure()
        {
            if (Validate())
            {
                int i = 0;

                this.tableInfo = new TableInfo();
                this.tableInfo.tableName = this.table.tableName;
                this.tableInfo.primaryKey = this.table.primaryKey;
                this.tableInfo.isAutoID = this.table.isAutoID;
                this.tableInfo.variantType = this.table.variantType;
                this.tableInfo.sqlDataType = this.table.sqlDataType;
                this.tableInfo.intLength = this.table.intLength;

                for (i = 0; i < this.lvColumn.Items.Count; i++)
                {
                    if (this.lvColumn.Items[i].Checked)
                        this.tableInfo.columnInfos.Add(this.table.GetColumnInfo(this.lvColumn.Items[i].Text));
                }

                ColumnInfo column = null;

                //Init Parameters
                NameSpace = txtNamespace.Text;
                BOObject = txtBOName.Text;
                if (txtBOName.Text.Trim().Length == 0)
                    BOObject = cmbTableName.Text.Trim() + "BO";

                BOObjectVariant = convertToVariant(BOObject);
                PrimaryKey = tableInfo.primaryKey;
                PrimaryKeyVariantNotDotValue = convertToVariant(PrimaryKey);
                PrimaryKeyVariant = convertToVariant(PrimaryKey);
                if (this.tableInfo.variantType != "string")
                    PrimaryKeyVariant += ".Value";
                PrimarySQLDataType = tableInfo.sqlDataType;
                KeyColumn = cmbKeyColumn.Text;
                ValueColumn = cmbValueColumn.Text;
                KeyDataType = tableInfo.GetColumnInfo(KeyColumn) != null ? tableInfo.GetColumnInfo(KeyColumn).variantType : null;
                if (KeyDataType != "string")
                    KeyDataType += "?";
                ValueDataType = tableInfo.GetColumnInfo(ValueColumn) != null ? tableInfo.GetColumnInfo(ValueColumn).variantType : null;

                CreateSP_GetTotalRows();
                CreateSP_GetBOList();

                if (chkBO.Checked)
                {
                    CreateSP_BOSelect();
                    CreateSP_BOInsert();
                    CreateSP_BOUpdate();
                    if (tableInfo.GetColumnInfo("DELE") != null)
                        CreateSP_BODelete();
                }

                if (chkBOCollection.Checked)
                    CreateSP_BOCollectionSelect();


                //Columns
                Columns = "";
                for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
                {
                    column = this.tableInfo.columnInfos[i];

                    Columns += "\r\nprivate " + column.variantType;
                    if (column.variantType != "string" && column.variantType != "byte[]")
                        Columns += "?";
                    Columns += " " + convertToVariant(column.columnName) + " = " + GetDefaultValueForVariant(column) + ";\r\n";

                    Columns += "public " + column.variantType;
                    if (column.variantType != "string" && column.variantType != "byte[]")
                        Columns += "?";

                    Columns += " CL_" + column.columnName + "\r\n";
                    Columns += "{\r\n";
                    Columns += "get { return this." + convertToVariant(column.columnName) + "; }\r\n";
                    Columns += "set { this." + convertToVariant(column.columnName) + " = value; PropertyHasChanged(\"CL_" + column.columnName + "\"); }\r\n";
                    Columns += "}\r\n";
                }

                //NewPrimaryKey
                NewPrimaryKey = "private static " + this.tableInfo.variantType + " new" + this.tableInfo.primaryKey + " = ";
                if (tableInfo.isAutoID)
                {
                    NewPrimaryKey += " -1;\r\n";
                    NewPrimaryKey += "private " + BOObject + "()\r\n";
                    NewPrimaryKey += "{\r\n";
                    NewPrimaryKey += "" + PrimaryKeyVariantNotDotValue + " = new" + this.tableInfo.primaryKey + "--;\r\n";
                    NewPrimaryKey += "}";
                }
                else
                {
                    MessageBox.Show("Khóa chỉnh phải là tự tăng (AutoID)");
                    return;
                }

                //Add_InsertParametters
                Add_InsertParametters = "";
                Init_InsertParametters = "";
                for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
                {
                    column = this.tableInfo.columnInfos[i];

                    if (!column.isAutoID)
                    {
                        switch (column.columnName)
                        {
                            case "USRC":
                                Init_InsertParametters += "this._USRC = Session.LoginID;\r\n";
                                break;
                            case "DATC":
                                Init_InsertParametters += "this._DATC = DateTime.MaxValue;\r\n";
                                break;
                            case "USRM":
                                Init_InsertParametters += "this._USRM = null;\r\n";
                                break;
                            case "DATM":
                                Init_InsertParametters += "this._DATM = null;\r\n";
                                break;
                            case "DELE":
                                Init_InsertParametters += "this._DELE = false;\r\n";
                                break;
                        }

                        if (column.columnName != "DATC" && column.columnName != "USRM" && column.columnName != "DATM" && column.columnName != "DELE")
                            Add_InsertParametters += "db.AddInParameter(cmd, \"@" + column.columnName + "\", SqlDbType." + column.sqlDataType + ", this." + convertToVariant(column.columnName) + ");\r\n";
                    }
                }

                if (this.tableInfo.isAutoID)
                    Add_InsertParametters += "db.AddOutParameter(cmd, \"@New" + this.tableInfo.primaryKey + "\", SqlDbType." + this.tableInfo.sqlDataType + ", " + this.tableInfo.intLength.ToString() + ");";
                if (this.tableInfo.HasColumn("DATC"))
                    Add_InsertParametters += "\r\ndb.AddOutParameter(cmd, \"@NewDATC\", SqlDbType.DateTime, 4);";

                //Add_Output
                Add_Output = "";
                if (this.tableInfo.isAutoID)
                    Add_Output = "this." + convertToVariant(this.tableInfo.primaryKey) + " = (" + this.tableInfo.variantType + ")cmd.Parameters[\"@New" + this.tableInfo.primaryKey + "\"].Value;";
                if (this.tableInfo.HasColumn("DATC"))
                    Add_Output += "\r\nthis." + convertToVariant("DATC") + " = (DateTime)cmd.Parameters[\"@NewDATC\"].Value;";


                //Add_UpdateParametters
                Add_UpdateParametters = "";
                Init_UpdateParametters = "";
                for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
                {
                    column = this.tableInfo.columnInfos[i];

                    switch (column.columnName)
                    {
                        case "USRM":
                            Init_UpdateParametters += "this._USRM = Session.LoginID;\r\n";
                            break;
                        case "DATM":
                            Init_UpdateParametters += "this._DATM = DateTime.Now;\r\n";
                            break;
                    }

                    if (column.columnName != "USRC" && column.columnName != "DATC" && column.columnName != "DATM")
                        Add_UpdateParametters += "db.AddInParameter(cmd, \"@" + column.columnName + "\", SqlDbType." + column.sqlDataType + ", this." + convertToVariant(column.columnName) + ");\r\n";
                }

                if (this.tableInfo.HasColumn("DATM"))
                    Add_UpdateParametters += "\r\ndb.AddOutParameter(cmd, \"@NewDATM\", SqlDbType.DateTime, 4);";

                //Add_Output
                Add_UpdateOutput = "";
                if (this.tableInfo.HasColumn("DATM"))
                    Add_UpdateOutput += "\r\nthis." + convertToVariant("DATM") + " = (DateTime)cmd.Parameters[\"@NewDATM\"].Value;";


                //Fetch Columns
                FetchColumns = "";
                for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
                {
                    column = this.tableInfo.columnInfos[i];
                    FetchColumns += String.Format(tmpFetchColumns, column.columnName, "this." + convertToVariant(column.columnName), column.variantType, column.columnName);
                }

                //DoInsert Columns
                DoInsertColumns = "";
                for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
                {
                    column = this.tableInfo.columnInfos[i];
                    if (!column.isAutoID)
                        DoInsertColumns += String.Format(tmpDoInsertColumns, column.columnName, column.sqlDataType, "this." + convertToVariant(column.columnName));
                }

                //DoUpdate Columns
                DoUpdateColumns = "";
                for (i = 0; i < this.tableInfo.columnInfos.Count; i++)
                {
                    column = this.tableInfo.columnInfos[i];
                    DoUpdateColumns += String.Format(tmpDoInsertColumns, column.columnName, column.sqlDataType, "this." + convertToVariant(column.columnName));
                }

                //CMD Condition
                CMDCondition = txtCondition.Text;
                //==========================================================
                //Generate
                string path = txtSolutionPath.Text;
                if (!path.EndsWith("\\"))
                    path += "\\";
                string modelPath = path + cmbTableName.Text + "\\";
                string listPath = path + "BOList\\";
                if (!Directory.Exists(modelPath))
                    Directory.CreateDirectory(modelPath);
                string pathBO = modelPath + txtBOName.Text + ".cs";
                string pathBOCollection = modelPath + txtBOCollectionName.Text + ".cs";

                if (!Directory.Exists(listPath))
                    Directory.CreateDirectory(listPath);
                string pathBOList = listPath + txtBOListName.Text + ".cs";

                string tmpPath = Application.StartupPath;
                if (!tmpPath.EndsWith("\\"))
                    tmpPath += "\\";
                if (!Directory.Exists(tmpPath))
                    Directory.CreateDirectory(tmpPath);
                string tmpPathBO = tmpPath + "SP_BOtmp.txt";
                string tmpPathBOCollection = tmpPath + "SP_BOCollectionTmp.txt";
                string tmpPathBOList = tmpPath + "SP_BOListTmp.txt";

                //BO
                if (chkBO.Checked)
                    createFile(tmpPathBO, pathBO);

                //BOCollection
                if (chkBOCollection.Checked)
                    createFile(tmpPathBOCollection, pathBOCollection);

                //BOList
                if (chkBOList.Checked)
                    createFile(tmpPathBOList, pathBOList);

                lblStatus.Visible = true;
                timer1.Enabled = true;
            }
        }
        //---------------------------------------------------------------
        #endregion function
    }
}